package com.vector.ding.web.service.bizinfo;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.List;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.github.pagehelper.PageHelper;
import com.vector.ding.commons.BizConstant;
import com.vector.ding.commons.util.NumberUtil;
import com.vector.ding.commons.util.StringUtil;
import com.vector.ding.commons.util.collection.CollectionUtil;
import com.vector.ding.commons.util.file.ExcelUtil;
import com.vector.ding.dao.mybatis.mapper.XdgsBizInfo2Mapper;
import com.vector.ding.dao.mybatis.model.XdgsBizInfo2;
import com.vector.ding.dao.mybatis.model.XdgsBizInfo2Example;
import com.vector.ding.exception.BaseException;

@Service
public class XdgsBizInfoMgrService {
	
	@Autowired
	private XdgsBizInfo2Mapper bizMapper;
	
	public List<XdgsBizInfo2> getBizInfo(int pageIndex, int pageSize, 
			HttpServletRequest request) {
		PageHelper.startPage(pageIndex, pageSize, true);
		XdgsBizInfo2Example example = parseBizInfoExample(request);
		//example.setOrderByClause("number asc");
		return bizMapper.selectByExample(example);
	}
	
	public XdgsBizInfo2 getBizInfo(String companyName) { 
		if (StringUtil.isEmpty(companyName))
			return null;
		
		XdgsBizInfo2Example example = new XdgsBizInfo2Example();
		   example.or().andCompanyNameEqualTo(companyName);
		List<XdgsBizInfo2> list = bizMapper.selectByExample(example);
		return CollectionUtil.isEmpty(list) ? null : list.get(0);
	}
	
	public List<XdgsBizInfo2> getBizInfos(String companyName) { 
		if (StringUtil.isEmpty(companyName))
			return null;
		
		XdgsBizInfo2Example example = new XdgsBizInfo2Example();
		   example.or().andCompanyNameEqualTo(companyName);
		return bizMapper.selectByExample(example);
	}
	
	public XdgsBizInfo2 getBizInfo(String companyName, int belongYear) { 
		if (StringUtil.isEmpty(companyName))
			return null;
		
		XdgsBizInfo2Example example = new XdgsBizInfo2Example();
		example.or().andCompanyNameEqualTo(companyName)
			.andBelongYearEqualTo(belongYear);
		List<XdgsBizInfo2> list = bizMapper.selectByExample(example);
		return CollectionUtil.isEmpty(list) ? null : list.get(0);
	}
	
	private XdgsBizInfo2Example parseBizInfoExample(HttpServletRequest request) {
		XdgsBizInfo2Example example = new XdgsBizInfo2Example();
		String companyName = StringUtil.getString(request.getParameter("company_name"));
		//if (StringUtil.isNotEmpty(companyName))
		example.or().andCompanyNameLike(StringUtil.wrapString(companyName, "%", "%"));
		return example;
	}
	
	public boolean addBizInfo(XdgsBizInfo2 record) {
		if (StringUtil.isEmpty(record.getNumber()))
			record.setNumber(UUID.randomUUID().toString());
		return 1 == bizMapper.insertSelective(record);
	}
	
	public boolean updateBizInfo(XdgsBizInfo2 record) {
		if (StringUtil.isEmpty(record.getNumber()))
			return false;
		return 1 == bizMapper.updateByPrimaryKeySelective(record);
	}
	
	public void deleteBizInfo(String... nums) {
		if (nums == null)
			return ;
		
		for (String num: nums) {
			bizMapper.deleteByPrimaryKey(num);
		}
	}
	
	public XdgsBizInfo2 parseBizInfo(HttpServletRequest request) {
		XdgsBizInfo2 record = new XdgsBizInfo2();
		record.setBelongYear(NumberUtil.getIntValue(request.getParameter("belongYear")));
		record.setCompanyName(request.getParameter("companyName"));
		record.setRegisterFund(NumberUtil.getBigDecimal(request.getParameter("registerFund")));
		record.setRegisterFundGrade(NumberUtil.getBigDecimal(request.getParameter("registerFundGrade")));
		record.setSuperviseRate(request.getParameter("superviseRate"));
		record.setSuperviseRateGrade(NumberUtil.getBigDecimal(request.getParameter("superviseRateGrade")));
		String roe = request.getParameter("roe");
		if(StringUtil.isNotEmpty(roe))
		   record.setRoe(new BigDecimal(roe));
		String roeGrade = request.getParameter("roeGrade");
		if(StringUtil.isNotEmpty(roeGrade))
		   record.setRoeGrade(new BigDecimal(roeGrade));
		String overduePercent = request.getParameter("overduePercent");
		if(StringUtil.isNotEmpty(overduePercent))
		   record.setOverduePercent(new BigDecimal(overduePercent));
		String overduePercentGrade = request.getParameter("overduePercentGrade");
		if(StringUtil.isNotEmpty(overduePercentGrade))
		   record.setOverduePercentGrade(new BigDecimal(overduePercentGrade));
		String grateOrgName = request.getParameter("grateOrgName");
		if(StringUtil.isNotEmpty(grateOrgName))
		   record.setGrateOrgName(grateOrgName);
		String grateOrgFinancingScale = request.getParameter("grateOrgFinancingScale");
		if(StringUtil.isNotEmpty(grateOrgFinancingScale))
		   record.setGrateOrgFinancingScale(new BigDecimal(grateOrgFinancingScale));
		String grateOrgGrade = request.getParameter("grateOrgGrade");
		if(StringUtil.isNotEmpty(grateOrgGrade))
		   record.setGrateOrgGrade(new BigDecimal(grateOrgGrade));
		if (StringUtil.isEmpty(request.getParameter("id")))
			record.setId(UUID.randomUUID().toString());
		else
			record.setId(request.getParameter("id"));
		record.setNumber(request.getParameter("number"));
		
		return record;
	}
	
	public void importFormFile(File importFile) {
		HSSFWorkbook workBook = null;
		int rowIndex = 0;
		try {
			workBook = new HSSFWorkbook(FileUtils.openInputStream(importFile));
			HSSFSheet sheet = workBook.getSheetAt(1);
			HSSFRow titleRow = sheet.getRow(4);
			for (int cellIdx = titleRow.getFirstCellNum(); cellIdx < titleRow.getLastCellNum(); ++cellIdx) {
				System.out.println(titleRow.getCell(cellIdx).toString());
			}
			int rowNum = sheet.getLastRowNum();
			for (rowIndex = 4; rowIndex <= rowNum; ++rowIndex) {
				HSSFRow row = sheet.getRow(rowIndex);
				boolean needImport = checkRowData(row);
				if (!needImport)
					continue;
				
				XdgsBizInfo2 bean = getBizInfoBean(row);
				bizMapper.insertSelective(bean);
			}
		} catch (Exception e) {
			e.printStackTrace();
//			throw new BaseException(e);
			throw new RuntimeException(String.format("第%s行数据格式有误，请检查数据！", rowIndex));
		} finally {
			if (workBook != null) {
				try {
					workBook.close();
				} catch (IOException e) {
					e.printStackTrace();
					throw new BaseException(e);
				}
			}
		}
	}
	
	public XdgsBizInfo2 getBizInfoBean(HSSFRow row) {
		XdgsBizInfo2 bean = new XdgsBizInfo2();
		bean.setId(UUID.randomUUID().toString());
		bean.setNumber(UUID.randomUUID().toString());
		bean.setCompanyName(ExcelUtil.getStringValue(row.getCell(BizConstant.Xedkgs.Biz2.COMPANY_NAME_CELL),false));
		bean.setBelongYear(ExcelUtil.getIntegerValue(row.getCell(BizConstant.Xedkgs.Biz2.BELONG_YEAR_CELL)));
		bean.setRegisterFund(new BigDecimal(row.getCell(BizConstant.Xedkgs.Biz2.REGISTER_FUND_CELL).getNumericCellValue()));
		bean.setRegisterFundGrade(new BigDecimal(row.getCell(BizConstant.Xedkgs.Biz2.REGISTER_FUND_GRADE_CELL).getNumericCellValue()));
		bean.setSuperviseRate(ExcelUtil.getStringValue(row.getCell(BizConstant.Xedkgs.Biz2.SUPERVISE_RATE_CELL),true));
		bean.setSuperviseRateGrade(new BigDecimal(row.getCell(BizConstant.Xedkgs.Biz2.SUPERVISE_RATE_GRADE_CELL).getNumericCellValue()));
		bean.setRoe(new BigDecimal(row.getCell(BizConstant.Xedkgs.Biz2.ROE_CELL).getNumericCellValue()));
		bean.setRoeGrade(new BigDecimal(row.getCell(BizConstant.Xedkgs.Biz2.ROE_GRADE_CELL).getNumericCellValue()));
		bean.setOverduePercent(new BigDecimal(row.getCell(BizConstant.Xedkgs.Biz2.OVERDUE_PERCENT_CELL).getNumericCellValue()));
		bean.setOverduePercentGrade(new BigDecimal(row.getCell(BizConstant.Xedkgs.Biz2.OVERDUE_PERCENT_GRADE_CELL).getNumericCellValue()));
		bean.setGrateOrgName(ExcelUtil.getStringValue(row.getCell(BizConstant.Xedkgs.Biz2.GRATE_ORG_NAME_CELL),false));
		bean.setGrateOrgFinancingScale(new BigDecimal(row.getCell(BizConstant.Xedkgs.Biz2.GRATE_ORG_FINANCING_SCALE_CELL).getNumericCellValue()));
		bean.setGrateOrgGrade(new BigDecimal(row.getCell(BizConstant.Xedkgs.Biz2.GRATE_ORG_GRADE_CELL).getNumericCellValue()));
		return bean;
	}
	
	private boolean checkRowData(HSSFRow row) {
		String companyName = row.getCell(BizConstant.Xedkgs.Biz2.COMPANY_NAME_CELL).toString();
		if (StringUtil.isEmpty(companyName))
			return false;
		
		XdgsBizInfo2Example example = new XdgsBizInfo2Example();
		example.or().andNumberEqualTo(companyName);
		return bizMapper.countByExample(example) == 0;
	}

	public BigDecimal getScore(String companyName, Integer belongYear) {
		XdgsBizInfo2 bizInfo = getBizInfo(companyName, belongYear);
		if (bizInfo == null)
			return new BigDecimal(0);
		
		BigDecimal score = new BigDecimal(0);
//		BigDecimal registerFundGrade = bizInfo.getRegisterFundGrade();
//		if (registerFundGrade != null)
//			score = score.add(registerFundGrade);
		BigDecimal superviseRateGrade = bizInfo.getSuperviseRateGrade();
		if (superviseRateGrade != null)
			score = score.add(superviseRateGrade);
		BigDecimal roeGrade = bizInfo.getRoeGrade();
		if (roeGrade != null)
			score = score.add(roeGrade);
		BigDecimal overduePercentGrade = bizInfo.getOverduePercentGrade();
		if (overduePercentGrade != null)
			score = score.add(overduePercentGrade);
		BigDecimal grateOrgGrade = bizInfo.getGrateOrgGrade();
		if (grateOrgGrade != null)
			score = score.add(grateOrgGrade);
		
		return score;
	}
	
	public BigDecimal getBaseScore(String companyName, Integer belongYear) {
		XdgsBizInfo2 bizInfo = getBizInfo(companyName, belongYear);
		if (bizInfo == null)
			return new BigDecimal(0);
		
		BigDecimal score = new BigDecimal(0);
		BigDecimal registerFundGrade = bizInfo.getRegisterFundGrade();
		if (registerFundGrade != null)
			score = score.add(registerFundGrade);
		
		return score;
	}
	
	public BigDecimal getScore(String companyName) {
		List<XdgsBizInfo2> list = getBizInfos(companyName);
		if (CollectionUtil.isEmpty(list))
			return new BigDecimal(0);
		
		BigDecimal score = new BigDecimal(0);
		for (XdgsBizInfo2 bizInfo: list) {
			score = score.add(getScore(bizInfo));
		}
		return score;
	}
	
	public BigDecimal getBaseScore(String companyName) {
		List<XdgsBizInfo2> list = getBizInfos(companyName);
		if (CollectionUtil.isEmpty(list))
			return new BigDecimal(0);
		
		BigDecimal score = new BigDecimal(0);
		for (XdgsBizInfo2 bizInfo: list) {
			BigDecimal registerFundGrade = bizInfo.getRegisterFundGrade();
			if (null != registerFundGrade)
				score = score.add(registerFundGrade);
		}
		return score;
	}
	
	public BigDecimal getScore(XdgsBizInfo2 bizInfo) {
		if (bizInfo == null)
			return new BigDecimal(0);
		
		BigDecimal score = new BigDecimal(0);
		/*BigDecimal registerFundGrade = bizInfo.getRegisterFundGrade();
		if (registerFundGrade != null)
			score = score.add(registerFundGrade);*/
		BigDecimal superviseRateGrade = bizInfo.getSuperviseRateGrade();
		if (superviseRateGrade != null)
			score = score.add(superviseRateGrade);
		BigDecimal roeGrade = bizInfo.getRoeGrade();
		if (roeGrade != null)
			score = score.add(roeGrade);
		BigDecimal overduePercentGrade = bizInfo.getOverduePercentGrade();
		if (overduePercentGrade != null)
			score = score.add(overduePercentGrade);
		BigDecimal grateOrgGrade = bizInfo.getGrateOrgGrade();
		if (grateOrgGrade != null)
			score = score.add(grateOrgGrade);
		
		return score;
	}

}
